<?php
namespace app\spadmin\library;
use PhpOffice\PhpSpreadsheet\IOFactory;
use think\Exception;
class Office {

    public static function excle($file){
        $excle = ROOT_PATH.DS.'public'.$file;
        try {
            $spreadsheet =IOFactory::load($excle);
            $worksheet =  $spreadsheet->getActiveSheet();
            $highestRow = $worksheet->getHighestRow();
            //$hightestDataRow = $worksheet->getHighestDataRow();
            $highestCol = 'L';
            $data = [];
            for ($row = 2; $row <= $highestRow; ++$row) {
                for ($col = 'A'; \ord($col) <= \ord($highestCol); ++$col) {
                    $line = $col . $row;
                    $text = $worksheet->getCell($line)->getValue();
                    $text = trim($text);
                    switch ($col) {
                        case 'A':
                            if(empty($text) || !is_numeric($text)){
                                throw new Exception($line.' 知识点错误');
                            }
                            $data[$row-2]['knowledge_id'] = $text;
                            break;
                        case 'B':
                            if(empty($text) || !is_numeric($text)){
                                throw new Exception($line.' 题组类目错误');
                            }
                            $data[$row-2]['type_id'] = $text;
                            break;
                        case 'C':
                            if(empty($text) ||!is_numeric($text)){
                                throw new Exception($line.' 题型错误');
                            }
                            $data[$row-2]['cate'] = $text;
                            break;
                        case 'D':
                            if(empty($text)){
                                throw new Exception($line.' 题干为空');
                            }
                            $data[$row-2]['topic'] = $text;
                            break;
                        case 'E':
                            if(!empty($text)){
                                $data[$row-2]['item'][] = $text;
                            }
                            
                            break;
                        case 'F':
                            if(!empty($text)){
                                $data[$row-2]['item'][] = $text;
                            }
                            break;
                        case 'G':
                            if(!empty($text)){
                                $data[$row-2]['item'][] = $text;
                            }
                            break;
                        case 'H':
                            if(!empty($text)){
                                $data[$row-2]['item'][] = $text;
                            }
                            break;
                        case 'I':
                            if(!empty($text)){
                                $data[$row-2]['item'][] = $text;
                            }
                            break;
                        case 'J':
                            if(!empty($text)){
                                $data[$row-2]['item'][] = $text;
                            }
                            break;
                        case 'K':
                            if(!empty($text)){
                                $data[$row-2]['answer'] = in_array($data[$row-2]['cate'],[2,3])? strtoupper($text) : $text ;
                            }
                            break;
                        case 'L':
                            if(!empty($text)){
                                $data[$row-2]['explain'] = $text;
                            }
                            break;
                    }       
                }
            }
            model('Exam')->saveAll($data);
            return true;
        } catch (Exception $e) {
            return $e->getMessage();
        } finally{
            @unlink($excle);
        }
        
    }
}